﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Text;
using System.Web;
using Framework;

namespace Enterprise
{
    public class VideoService : BaseService
    {  /// <summary>
        /// 获取视频列表
        /// </summary>
        /// <returns></returns>
        public Table GetTable(string name, int videoCategoryId, string language, int pageValue, int rowsValue)
        {
            var sbSqlWhere = new StringBuilder();
            sbSqlWhere.Append(" where A.CoName like '%'+@CoName+'%' and A.CoLanguage like '%'+@CoLanguage+'%' ");
            if (videoCategoryId != 0)
            {
                sbSqlWhere.Append(" and A.CoVideoCategoryId=@CoVideoCategoryId ");
            }
            var parameters = new[]
            {
                new SqlParameter("@CoName", SqlDbType.VarChar, 255),
                new SqlParameter("@CoLanguage", SqlDbType.VarChar, 255),
                new SqlParameter("@CoVideoCategoryId", SqlDbType.Int)
            };
            parameters[0].Value = name.SqlLike();
            parameters[1].Value = language.SqlLike();
            parameters[2].Value = videoCategoryId;
            int total = DbHelperSql.GetSingle("select count(*) from TbVideo as A {0}".FormatWith(sbSqlWhere), parameters).SafeToInt();
            var sbSql = new StringBuilder();
            sbSql.AppendFormat("select top {0} A.CoVideoId,A.CoNormalImage,A.CoSmallImage,A.CoBigImage,A.CoName,A.CoVideoCategoryId,A.CoIsRecommend,A.CoIsPopular,A.CoIsTop,A.CoViews,A.CoAuthor,A.CoDate,A.CoIsReserved,A.CoReservedUrl,A.CoLanguage, B.CoName as CoCategoryName from TbVideo as A left join TbVideoCategory as B on  A.CoVideoCategoryId=B.CoVideoCategoryId {1} ", rowsValue, sbSqlWhere);
            if (pageValue > 1)
                sbSql.AppendFormat(" and A.CoVideoId not in (select top {0} A.CoVideoId from TbVideo as A {1} order by A.CoVideoId) ", rowsValue * (pageValue - 1), sbSqlWhere);
            sbSql.Append(" order by A.CoVideoId");
            var ds = DbHelperSql.Query(sbSql.ToString(), parameters);
            var table = new Table
            {
                Rows = ds.Tables["ds"].Rows,
                Total = total
            };
            return table;
        }
        /// <summary>
        /// 根据ID获取一条记录
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public DataRow GetById(int id)
        {
            return DbHelperSql.Query("select * from TbVideo where CoVideoId={0}".FormatWith(id)).Tables["ds"].Rows[0];
        }
        public int Add(int videoCategoryId, string name, string content, string summary, string author, string language, string reservedUrl, DateTime date, bool isRecommend, bool isReserved, bool isPopular, bool isTop, HttpPostedFileBase filedata)
        {
            Check.Argument.IsNotOutOfLength(name, 2, 50, "视频名称");
            Check.Argument.IsNotOutOfLength(summary, 252, "视频简介");
            Check.Argument.IsNotOutOfLength(author, 20, "作者");
            Check.Argument.IsNotOutOfLength(language, 20, "视频语言");
            Check.Argument.IsNotOutOfLength(reservedUrl, 252, "转载地址");
            Check.Argument.IsNotInvalidSqlDateTime(date, "发布日期");
            if (filedata.ContentLength <= 0)
            {
                throw new ArgumentException("封面图片不能为空");
            }
            var ext = Path.GetExtension(filedata.FileName);
            var path = HttpContext.Current.Server.MapPath("~/Upload/Video/Image/");
            if (!Directory.Exists(path))
            {
                Directory.CreateDirectory(path);
            }
            var fileName = Guid.NewGuid() + ext;
            var thumFileName = "thum_" + fileName;
            var filePath = Path.Combine(path, fileName);
            var thumFilePath = Path.Combine(path, thumFileName);
            filedata.SaveAs(filePath);
            ImageExtension.ToThumbnail(filePath, thumFilePath, 185, 165);
            var sbSql = new StringBuilder();
            sbSql.Append("insert into TbVideo(");
            sbSql.Append("CoVideoCategoryId,CoCategoryPath,CoName,CoContent,CoSummary,CoReservedUrl,CoAuthor,CoLanguage,CoDate,CoIsReserved,CoIsPopular,CoIsTop,CoIsRecommend,CoViews,CoNormalImage,CoSmallImage,CoBigImage");
            sbSql.Append(")");
            sbSql.Append(" values (");
            sbSql.Append("@CoVideoCategoryId,@CoCategoryPath,@CoName,@CoContent,@CoSummary,@CoReservedUrl,@CoAuthor,@CoLanguage,@CoDate,@CoIsReserved,@CoIsPopular,@CoIsTop,@CoIsRecommend,@CoViews,@CoNormalImage,@CoSmallImage,@CoBigImage");
            sbSql.Append(")");
            SqlParameter[] parameters =
                {
                    new SqlParameter("@CoVideoCategoryId", SqlDbType.Int),
                    new SqlParameter("@CoCategoryPath", SqlDbType.VarChar),
                    new SqlParameter("@CoName", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoContent", SqlDbType.VarChar),
                    new SqlParameter("@CoSummary", SqlDbType.VarChar),
                    new SqlParameter("@CoReservedUrl", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoAuthor", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoLanguage", SqlDbType.VarChar, 10),
                    new SqlParameter("@CoDate", SqlDbType.DateTime),
                    new SqlParameter("@CoIsReserved",SqlDbType.Bit),
                    new SqlParameter("@CoIsPopular",SqlDbType.Bit),
                    new SqlParameter("@CoIsTop",SqlDbType.Bit),
                    new SqlParameter("@CoIsRecommend",SqlDbType.Bit),
                    new SqlParameter("@CoViews", SqlDbType.Int),
                    new SqlParameter("@CoNormalImage", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoSmallImage", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoBigImage", SqlDbType.VarChar, 255)
                };
            using (var sdr = DbHelperSql.ExecuteReader("select * from TbVideoCategory where CoVideoCategoryId={0}".FormatWith(videoCategoryId)))
            {
                if (sdr.Read())
                {
                    parameters[0].Value = videoCategoryId;
                    parameters[1].Value = sdr["CoCategoryPath"].SafeToString();
                }
                else
                {
                    parameters[0].Value = DBNull.Value;
                    parameters[1].Value = "";
                }
            }
            parameters[2].Value = name;
            parameters[3].Value = content;
            parameters[4].Value = summary;
            parameters[5].Value = reservedUrl;
            parameters[6].Value = author;
            parameters[7].Value = language;
            parameters[8].Value = date;
            parameters[9].Value = isReserved;
            parameters[10].Value = isPopular;
            parameters[11].Value = isTop;
            parameters[12].Value = isRecommend;
            parameters[13].Value = 0;
            parameters[14].Value = fileName;
            parameters[15].Value = thumFileName;
            parameters[16].Value = fileName;
            var obj = DbHelperSql.Add(sbSql.ToString(), parameters).SafeToInt32();
            return obj;
        }
        /// <summary>
        /// 编辑视频
        /// </summary>
        /// <param name="id"></param>
        /// <param name="videoCategoryId"></param>
        /// <param name="name"></param>
        /// <param name="content"></param>
        /// <param name="summary"></param>
        /// <param name="author"></param>
        /// <param name="language"></param>
        /// <param name="reservedUrl"></param>
        /// <param name="date"></param>
        /// <param name="isRecommend"></param>
        /// <param name="isReserved"></param>
        /// <param name="isPopular"></param>
        /// <param name="isTop"></param>
        /// <param name="filedata"></param>
        /// <returns></returns>
        public int Edit(int id, int videoCategoryId, string name, string content, string summary, string author, string language, string reservedUrl, DateTime date, bool isRecommend, bool isReserved, bool isPopular, bool isTop, HttpPostedFileBase filedata)
        {
            Check.Argument.IsNotOutOfLength(name, 2, 50, "视频名称");
            Check.Argument.IsNotOutOfLength(summary, 252, "视频简介");
            Check.Argument.IsNotOutOfLength(author, 20, "作者");
            Check.Argument.IsNotOutOfLength(language, 20, "视频语言");
            Check.Argument.IsNotOutOfLength(reservedUrl, 252, "转载地址");
            Check.Argument.IsNotInvalidSqlDateTime(date, "发布日期");
            SqlParameter[] parameters;
            var sbSql = new StringBuilder();
            sbSql.Append("update TbVideo set ");
            sbSql.Append("CoVideoCategoryId=@CoVideoCategoryId");
            sbSql.Append(",");
            sbSql.Append("CoCategoryPath=@CoCategoryPath");
            sbSql.Append(",");
            sbSql.Append("CoName=@CoName");
            sbSql.Append(",");
            sbSql.Append("CoContent=@CoContent");
            sbSql.Append(",");
            sbSql.Append("CoSummary=@CoSummary");
            sbSql.Append(",");
            sbSql.Append("CoReservedUrl=@CoReservedUrl");
            sbSql.Append(",");
            sbSql.Append("CoAuthor=@CoAuthor");
            sbSql.Append(",");
            sbSql.Append("CoLanguage=@CoLanguage");
            sbSql.Append(",");
            sbSql.Append("CoDate=@CoDate");
            sbSql.Append(",");
            sbSql.Append("CoIsReserved=@CoIsReserved");
            sbSql.Append(",");
            sbSql.Append("CoIsPopular=@CoIsPopular");
            sbSql.Append(",");
            sbSql.Append("CoIsTop=@CoIsTop");
            sbSql.Append(",");
            sbSql.Append("CoIsRecommend=@CoIsRecommend");
            if (filedata != null && filedata.ContentLength > 0)
            {
                sbSql.Append(",");
                sbSql.Append("CoNormalImage=@CoNormalImage");
                sbSql.Append(",");
                sbSql.Append("CoSmallImage=@CoSmallImage");
                sbSql.Append(",");
                sbSql.Append("CoBigImage=@CoBigImage");
                parameters = new[]
                    {
                        new SqlParameter("@CoVideoCategoryId", SqlDbType.Int),
                        new SqlParameter("@CoCategoryPath", SqlDbType.VarChar),
                        new SqlParameter("@CoName", SqlDbType.VarChar, 255),
                        new SqlParameter("@CoContent", SqlDbType.VarChar),
                        new SqlParameter("@CoSummary", SqlDbType.VarChar),
                        new SqlParameter("@CoReservedUrl", SqlDbType.VarChar, 255),
                        new SqlParameter("@CoAuthor", SqlDbType.VarChar, 255),
                        new SqlParameter("@CoLanguage", SqlDbType.VarChar, 10),
                        new SqlParameter("@CoDate", SqlDbType.DateTime),
                        new SqlParameter("@CoIsReserved", SqlDbType.Bit),
                        new SqlParameter("@CoIsPopular", SqlDbType.Bit),
                        new SqlParameter("@CoIsTop", SqlDbType.Bit),
                        new SqlParameter("@CoIsRecommend", SqlDbType.Bit),
                        new SqlParameter("@CoNormalImage", SqlDbType.VarChar, 255),
                        new SqlParameter("@CoSmallImage", SqlDbType.VarChar, 255),
                        new SqlParameter("@CoBigImage", SqlDbType.VarChar, 255),
                        new SqlParameter("@id", SqlDbType.Int)
                    };
                using (var sdr = DbHelperSql.ExecuteReader("select * from TbVideoCategory where CoVideoCategoryId={0}".FormatWith(videoCategoryId)))
                {
                    if (sdr.Read())
                    {
                        parameters[0].Value = videoCategoryId;
                        parameters[1].Value = sdr["CoCategoryPath"].SafeToString();
                    }
                    else
                    {
                        parameters[0].Value = DBNull.Value;
                        parameters[1].Value = "";
                    }
                }
                parameters[2].Value = name;
                parameters[3].Value = content;
                parameters[4].Value = summary;
                parameters[5].Value = reservedUrl;
                parameters[6].Value = author;
                parameters[7].Value = language;
                parameters[8].Value = date;
                parameters[9].Value = isReserved;
                parameters[10].Value = isPopular;
                parameters[11].Value = isTop;
                parameters[12].Value = isRecommend;
                var ext = Path.GetExtension(filedata.FileName);
                var path = HttpContext.Current.Server.MapPath("~/Upload/Video/Image/");
                if (!Directory.Exists(path))
                {
                    Directory.CreateDirectory(path);
                }
                var fileName = Guid.NewGuid() + ext;
                var thumFileName = "thum_" + fileName;
                var filePath = Path.Combine(path, fileName);
                var thumFilePath = Path.Combine(path, thumFileName);
                filedata.SaveAs(filePath);
                ImageExtension.ToThumbnail(filePath, thumFilePath, 185, 165);
                var ds = DbHelperSql.Query("select * from TbVideo where CoVideoId={0}".FormatWith(id));
                foreach (DataRow item in ds.Tables["ds"].Rows)
                {
                    FileHelper.DeleteSub(Path.Combine(path, item["CoNormalImage"].SafeToString()));
                    FileHelper.DeleteSub(Path.Combine(path, item["CoSmallImage"].SafeToString()));
                    FileHelper.DeleteSub(Path.Combine(path, item["CoBigImage"].SafeToString()));
                }
                parameters[13].Value = fileName;
                parameters[14].Value = thumFileName;
                parameters[15].Value = fileName;
                parameters[16].Value = id;
            }
            else
            {
                parameters = new[]
                {
                    new SqlParameter("@CoVideoCategoryId", SqlDbType.Int),
                    new SqlParameter("@CoCategoryPath", SqlDbType.VarChar),
                    new SqlParameter("@CoName", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoContent", SqlDbType.VarChar),
                    new SqlParameter("@CoSummary", SqlDbType.VarChar),
                    new SqlParameter("@CoReservedUrl", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoAuthor", SqlDbType.VarChar, 255),
                    new SqlParameter("@CoLanguage", SqlDbType.VarChar, 10),
                    new SqlParameter("@CoDate", SqlDbType.DateTime),
                    new SqlParameter("@CoIsReserved",SqlDbType.Bit),
                    new SqlParameter("@CoIsPopular",SqlDbType.Bit),
                    new SqlParameter("@CoIsTop",SqlDbType.Bit),
                    new SqlParameter("@CoIsRecommend",SqlDbType.Bit),  
                    new SqlParameter("@id", SqlDbType.Int)
                };
                using (var sdr = DbHelperSql.ExecuteReader("select * from TbVideoCategory where CoVideoCategoryId={0}".FormatWith(videoCategoryId)))
                {
                    if (sdr.Read())
                    {
                        parameters[0].Value = videoCategoryId;
                        parameters[1].Value = sdr["CoCategoryPath"].SafeToString();
                    }
                    else
                    {
                        parameters[0].Value = DBNull.Value;
                        parameters[1].Value = "";
                    }
                }
                parameters[2].Value = name;
                parameters[3].Value = content;
                parameters[4].Value = summary;
                parameters[5].Value = reservedUrl;
                parameters[6].Value = author;
                parameters[7].Value = language;
                parameters[8].Value = date;
                parameters[9].Value = isReserved;
                parameters[10].Value = isPopular;
                parameters[11].Value = isTop;
                parameters[12].Value = isRecommend;
                parameters[13].Value = id;
            }
            sbSql.Append(" where CoVideoId=@id;");
            var obj = DbHelperSql.ExecuteSql(sbSql.ToString(), parameters).SafeToInt();
            return obj;

        }
        /// <summary>
        /// 删除视频
        /// </summary>
        /// <param name="id"></param>
        public void Delete(int id)
        {
            DbHelperSql.ExecuteSql("delete from TbVideo where CoVideoId={0}".FormatWith(id));
        }
    }
}
